﻿using software.business.develop;
using software.common;
using software.config;
using software.config.model;
using software.connect;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Windows;
using System.Windows.Controls;
using Tian.Common.Texts;
using Tian.Common.Verify;

namespace software.winwpfs.develop
{
    /// <summary>
    /// CreateSqlByBase.xaml 的交互逻辑
    /// </summary>
    public partial class CreateSqlByBase : Page
    {
        private ISqlConnect sqlConnect;
        public CreateSqlByBase()
        {
            InitializeComponent();
        }
        private List<DataBase> dataBase = AppSetting.dataBase;
        private CreateSqlUtil createSqlUtil = new CreateSqlUtil();

        private void Page_Loaded(object sender, RoutedEventArgs e)
        {
            List<DataBaseVM> list = new List<DataBaseVM>();
            foreach (DataBase item in dataBase)
            {
                list.Add(new DataBaseVM() { Name = item.Name, Key = item.Key });
            }
            this.DataBaseList.ItemsSource = list;
        }
        private void ButChooseBase(object sender, RoutedEventArgs e)
        {
            string key = this.DataBaseList.SelectedValue.ToString();
            sqlConnect = SqlFactory.GetSqlConnect(key);
            if (sqlConnect == null)
            {
                MessageBox.Show("连接失败");
            }
            else
            {
                MessageBox.Show("连接成功");
            }

        }

        private void ButSelectData(object sender, RoutedEventArgs e)
        {
            if (sqlConnect == null)
            {
                HandyControl.Controls.MessageBox.Error("请先连接数据库！", "操作提示");
                return;
            }
            string selectSql = SelectSql.Text;
            DataTable dataTable = sqlConnect.GetTable(selectSql);
            ViewTable.ItemsSource = dataTable.DefaultView;
        }

        private void ButCreateSql(object sender, RoutedEventArgs e)
        {
            if (sqlConnect == null)
            {
                HandyControl.Controls.MessageBox.Error("请先连接数据库！", "操作提示");
                return;
            }
            string tableName = TableName.Text.Trim();
            DataTable table = (ViewTable.ItemsSource as DataView).ToTable();
            int dtColumnCount = table.Columns.Count;//原本列数
            int dtRowCount = table.Rows.Count;//原本行数
            List<string> sqllist = new List<string>();
            string ignoreField = IgnoreField.Text.Trim();
            List<string> ignoreFields = new List<string>();
            if (!CharVerify.IsEmpty(ignoreField))
            {
                ignoreFields.AddRange(ignoreField.Split(','));
            }
            //获取空为null字段
            string emptyIsNull = EmptyIsNull.Text.Trim();
            List<string> emptyIsNulls = new List<string>();
            if (!CharVerify.IsEmpty(emptyIsNull))
            {
                emptyIsNulls.AddRange(emptyIsNull.Split(','));
            }
            //获取需要特殊转义的字段
            string escapeField = EscapeField.Text.Trim();
            List<string> escapeFields = new List<string>();
            if (!CharVerify.IsEmpty(escapeField))
            {
                escapeFields.AddRange(escapeField.Split(','));
            }
            createSqlUtil.emptyIsNulls = emptyIsNulls;
            createSqlUtil.escapeFields = escapeFields;
            List<string> columns = new List<string>();//最终字段
            for (int i = 0; i < dtColumnCount; i++)
            {
                //排除过滤字段
                if (!ignoreFields.Contains(table.Columns[i].ColumnName))
                {
                    columns.Add(table.Columns[i].ColumnName);
                }

            }
            int realtColumnCount = columns.Count;//去除过滤字段后的列数
            if (InsertCheck.IsChecked == true)
            {
                for (int i = 0; i < dtRowCount; i++)
                {
                    List<string> values = new List<string>();
                    for (int j = 0; j < realtColumnCount; j++)
                    {
                        values.Add(createSqlUtil.GetValueByColumnValue(columns[j], table.Rows[i][columns[j]]));
                    }
                    //拼写sql
                    string sql_one = string.Format("insert into {0}({1}) values({2});", tableName, string.Join(",", columns)
                        , string.Join(",", values));
                    sqllist.Add(sql_one);
                }
            }
            if (UpdateCheck.IsChecked == true)
            {
                string tableKey = TableKey.Text.Trim();
                if (CharVerify.IsEmpty(tableKey))
                {
                    HandyControl.Controls.MessageBox.Error("主键不能为空！", "操作提示");
                    return;
                }
                List<string> tableKeys = new List<string>();
                tableKeys.AddRange(tableKey.Split(","));
                //移除条件字段
                for (int i = 0; i < columns.Count; i++)
                {
                    if (tableKeys.Contains(columns[i]))
                    {
                        columns.Remove(columns[i]);
                    }
                }
                //创建更新语句
                for (int i = 0; i < dtRowCount; i++)
                {
                    string setValue = "set";
                    for (int j = 0; j < columns.Count; j++)
                    {
                        setValue += string.Format(" {0} = '{1}',", columns[j], table.Rows[i][columns[j]].ToString());
                    }
                    setValue = StringUtil.RemoveLastChar(setValue);
                    string setWhere = " where";
                    for (int j = 0; j < tableKeys.Count; j++)
                    {
                        setWhere += string.Format(" and {0} = '{1}'", tableKeys[j], table.Rows[i][tableKeys[j]].ToString());
                    }
                    setWhere = setWhere.Remove(7, 3);
                    string sql_one = string.Format("update {0} {1} {2};", tableName
                    , setValue, setWhere);
                    sqllist.Add(sql_one);
                }
            }
            //写入文本及导出
            string lujing = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "插入语句.txt");
            File.WriteAllLines(lujing, sqllist);
            MessageBoxResult messageBoxResult = HandyControl.Controls.MessageBox.Show("已生成，需要打开吗？", "打开文件", MessageBoxButton.YesNo, MessageBoxImage.Question);
            if (messageBoxResult == MessageBoxResult.Yes)
            {
                ProcessStartInfo startInfo = new ProcessStartInfo
                {
                    FileName = lujing,
                    UseShellExecute = true
                };
                Process.Start(startInfo);
            }
        }

        private void UpdateCheck_Checked(object sender, RoutedEventArgs e)
        {
            if (InsertCheck.IsChecked == true)
            {
                InsertCheck.IsChecked = false;
            }
        }

        private void InsertCheck_Checked(object sender, RoutedEventArgs e)
        {
            if (UpdateCheck.IsChecked == true)
            {
                UpdateCheck.IsChecked = false;
            }
        }
    }
}
